import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Objects;

/**
 * JPA Criteria API 测试
 *
 * @author luohq
 * @version 1.0.0
 * @date 2022-07-19 15:41
 */
public class MyDataRepositoryCriteriaApiTest extends BaseTest {

    private static final Logger log = LoggerFactory.getLogger(MyDataRepositoryCriteriaApiTest.class);

    @Resource
    private EntityManager entityManager;

    @Test
    void testCriteriaApi_selectOrderBy() {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        //创建query，指定返回对象类型
        CriteriaQuery<MyData> cq = cb.createQuery(MyData.class);
        Root<MyData> root = cq.from(MyData.class);

        /**
         * select id, my_name, my_type
         * from my_data
         * where my_type >= 1
         * order by create_time desc, my_type asc
         */
        cq
                //select所有属性（列出具体属性，并非*）
                //.select(root)
                //select指定属性（且Entity对象需要有对应列属性的构造函数，否则报错, 如MyData(Long, String, Integer)）
                .multiselect(root.get("id"), root.get("myName"), root.get("myType"))
                .where(cb.greaterThanOrEqualTo(root.get("myType"), 1))
                .orderBy(cb.desc(root.get("createTime")), cb.asc(root.get("myType")));


        TypedQuery<MyData> tq = entityManager.createQuery(cq);
        List<MyData> resultList = tq.getResultList();
        //MyData singleResult = query.getSingleResult();
        log.info("myDataList size: {}, data: {}", resultList.size(), resultList);
        Assertions.assertEquals(2, resultList.size());
    }

    @Test
    void testCriteriaApi_selectCount() {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        //创建query，指定返回对象类型
        CriteriaQuery<Long> cq = cb.createQuery(Long.class);
        //查询实体类
        Root<MyData> root = cq.from(MyData.class);

        /**
         * select count(id)
         * from my_data
         * where my_type >= 1
         */
        cq
                //select指定属性：count(id)
                .select(cb.count(root))
                //select指定属性：count(my_type)
                //.select(cb.count(root.get("myType")))
                .where(cb.greaterThanOrEqualTo(root.get("myType"), 1));


        TypedQuery<Long> tq = entityManager.createQuery(cq);
        Long countResult = tq.getSingleResult();
        log.info("myData count: {}", countResult);
        Assertions.assertEquals(2, countResult);
    }

    @Test
    void testCriteriaApi_groupByHaving() {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        //创建query，指定返回对象类型
        CriteriaQuery<Object[]> cq = cb.createQuery(Object[].class);
        //查询实体类
        Root<MyData> root = cq.from(MyData.class);

        /**
         * select my_type, count(my_type)
         * from my_data
         * where my_type >= 1
         * group by my_type
         * having my_type >= 1
         * order by my_type asc
         */
        cq
                //select指定属性：my_type, count(id)
                //.multiselect(root.get("myType"), cb.count(root))
                //select指定属性：my_type, count(my_type)
                .multiselect(root.get("myType"), cb.count(root.get("myType")))
                .where(cb.greaterThanOrEqualTo(root.get("myType"), 1))
                .groupBy(root.get("myType"))
                .having(cb.greaterThanOrEqualTo(root.get("myType"), 1))
                .orderBy(cb.asc(root.get("myType")));


        TypedQuery<Object[]> tq = entityManager.createQuery(cq);
        List<Object[]> resultList = tq.getResultList();
        log.info("myDataList size: {}, data: {}", resultList.size(), resultList);
        Assertions.assertEquals(2, resultList.size());

        resultList.forEach(resultItems -> {
            Integer myType = (Integer) resultItems[0];
            Long count = (Long) resultItems[1];
            log.info("myType: {}, count: {}", myType, count);
        });
    }


    @Test
    void testCriteriaApi_queryWhereCombination() {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        //创建query，指定返回对象类型
        CriteriaQuery<MyData> cq = cb.createQuery(MyData.class);
        Root<MyData> root = cq.from(MyData.class);

        /**
         * select id, my_name, my_type, create_time, create_by
         * from my_data
         * where my_type >= 1 and (id = 1 or my_name like '%lu%')
         * order by create_time desc
         */
        Predicate idPredicate = cb.equal(root.get("id"), 1);
        Predicate myNamePredicate = cb.like(root.get("myName"), JpaUtil.like("lu"));
        Predicate orPredicate = cb.or(idPredicate, myNamePredicate);

        Predicate myTypePredicate = cb.greaterThanOrEqualTo(root.get("myType"), 1);
        Predicate andPredicate = cb.and(myTypePredicate, orPredicate);

        cq
                //select所有属性（列出具体属性，并非*）
                .select(root)
                .where(andPredicate)
                .orderBy(cb.desc(root.get("createTime")));


        TypedQuery<MyData> tq = entityManager.createQuery(cq);
        List<MyData> resultList = tq.getResultList();
        //MyData singleResult = query.getSingleResult();
        log.info("myDataList size: {}, data: {}", resultList.size(), resultList);
        Assertions.assertEquals(1, resultList.size());
    }

    @Test
    void testCriteriaApi_queryWhereDynamicConjunction() {
        MyDataQueryDto myDataQueryDto = this.buildMyDataQueryDto();

        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        //创建query，指定返回对象类型
        CriteriaQuery<MyData> cq = cb.createQuery(MyData.class);
        Root<MyData> root = cq.from(MyData.class);

        /**
         * select id, my_name, my_type, create_time, create_by
         * from my_data
         * where
         * id = 1
         * and my_name like '%lu%'
         * and my_type = 1
         * and create_time >= '2022-01-01 18:00:20' and create_time <= '2122-01-01 18:00:20'
         * order by create_time desc
         */
        Predicate conjunctionPredicate = cb.conjunction();
        List<Expression<Boolean>> expressions = conjunctionPredicate.getExpressions();
        if (Objects.nonNull(myDataQueryDto.getId())) {
            expressions.add(cb.equal(root.get("id"), myDataQueryDto.getId()));
        }
        if (StringUtils.hasText(myDataQueryDto.getMyName())) {
            expressions.add(cb.like(root.get("myName"), JpaUtil.like(myDataQueryDto.getMyName())));
        }
        if (Objects.nonNull(myDataQueryDto.getMyType())) {
            expressions.add(cb.equal(root.get("myType"), myDataQueryDto.getMyType()));
        }
        if (Objects.nonNull(myDataQueryDto.getCreateTimeStart())) {
            expressions.add(cb.greaterThanOrEqualTo(root.get("createTime"), myDataQueryDto.getCreateTimeStart()));
        }
        if (Objects.nonNull(myDataQueryDto.getCreateTimeEnd())) {
            expressions.add(cb.lessThanOrEqualTo(root.get("createTime"), myDataQueryDto.getCreateTimeEnd()));
        }

        cq
                //select所有属性（列出具体属性，并非*）
                .select(root)
                .where(conjunctionPredicate)
                .orderBy(cb.desc(root.get("createTime")));


        TypedQuery<MyData> tq = entityManager.createQuery(cq);
        List<MyData> resultList = tq.getResultList();
        //MyData singleResult = query.getSingleResult();
        log.info("myDataList size: {}, data: {}", resultList.size(), resultList);
        Assertions.assertEquals(1, resultList.size());
    }

    @Test
    void testCriteriaApi_queryWhereDynamicDisjunction() {
        MyDataQueryDto myDataQueryDto = this.buildMyDataQueryDto();

        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        //创建query，指定返回对象类型
        CriteriaQuery<MyData> cq = cb.createQuery(MyData.class);
        Root<MyData> root = cq.from(MyData.class);

        /**
         * select id, my_name, my_type, create_time, create_by
         * from my_data
         * where
         * id = 1
         * or my_name like '%lu%'
         * or my_type = 1
         * or create_time >= '2022-01-01 18:00:20' and create_time <= '2122-01-01 18:00:20'
         * order by create_time desc
         */
        Predicate disjunctionPredicate = cb.disjunction();
        List<Expression<Boolean>> expressions = disjunctionPredicate.getExpressions();
        if (Objects.nonNull(myDataQueryDto.getId())) {
            expressions.add(cb.equal(root.get("id"), myDataQueryDto.getId()));
        }
        if (StringUtils.hasText(myDataQueryDto.getMyName())) {
            expressions.add(cb.like(root.get("myName"), JpaUtil.like(myDataQueryDto.getMyName())));
        }
        if (Objects.nonNull(myDataQueryDto.getMyType())) {
            expressions.add(cb.equal(root.get("myType"), myDataQueryDto.getMyType()));
        }
        if (Objects.nonNull(myDataQueryDto.getCreateTimeStart())) {
            expressions.add(cb.greaterThanOrEqualTo(root.get("createTime"), myDataQueryDto.getCreateTimeStart()));
        }
        if (Objects.nonNull(myDataQueryDto.getCreateTimeEnd())) {
            expressions.add(cb.lessThanOrEqualTo(root.get("createTime"), myDataQueryDto.getCreateTimeEnd()));
        }

        cq
                //select所有属性（列出具体属性，并非*）
                .select(root)
                .where(disjunctionPredicate)
                .orderBy(cb.desc(root.get("createTime")));


        TypedQuery<MyData> tq = entityManager.createQuery(cq);
        List<MyData> resultList = tq.getResultList();
        //MyData singleResult = query.getSingleResult();
        log.info("myDataList size: {}, data: {}", resultList.size(), resultList);
        Assertions.assertEquals(2, resultList.size());
    }

    @Test
    void testCriteriaApi_queryWhereDynamicAnd() {
        MyDataQueryDto myDataQueryDto = this.buildMyDataQueryDto();

        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        //创建query，指定返回对象类型
        CriteriaQuery<MyData> cq = cb.createQuery(MyData.class);
        Root<MyData> root = cq.from(MyData.class);

        /**
         * select id, my_name, my_type, create_time, create_by
         * from my_data
         * where
         * id = 1
         * and my_name like '%lu%'
         * and my_type = 1
         * and create_time >= '2022-01-01 18:00:20' and create_time <= '2122-01-01 18:00:20'
         * order by create_time desc
         */

        List<Predicate> predicates = new ArrayList<>(5);
        if (Objects.nonNull(myDataQueryDto.getId())) {
            predicates.add(cb.equal(root.get("id"), myDataQueryDto.getId()));
        }
        if (StringUtils.hasText(myDataQueryDto.getMyName())) {
            predicates.add(cb.like(root.get("myName"), JpaUtil.like(myDataQueryDto.getMyName())));
        }
        if (Objects.nonNull(myDataQueryDto.getMyType())) {
            predicates.add(cb.equal(root.get("myType"), myDataQueryDto.getMyType()));
        }
        if (Objects.nonNull(myDataQueryDto.getCreateTimeStart())) {
            predicates.add(cb.greaterThanOrEqualTo(root.get("createTime"), myDataQueryDto.getCreateTimeStart()));
        }
        if (Objects.nonNull(myDataQueryDto.getCreateTimeEnd())) {
            predicates.add(cb.lessThanOrEqualTo(root.get("createTime"), myDataQueryDto.getCreateTimeEnd()));
        }
        Predicate andPredicate = cb.and(predicates.stream().toArray(Predicate[]::new));

        cq
                //select所有属性（列出具体属性，并非*）
                .select(root)
                .where(andPredicate)
                .orderBy(cb.desc(root.get("createTime")));


        TypedQuery<MyData> tq = entityManager.createQuery(cq);
        List<MyData> resultList = tq.getResultList();
        //MyData singleResult = query.getSingleResult();
        log.info("myDataList size: {}, data: {}", resultList.size(), resultList);
        Assertions.assertEquals(1, resultList.size());
    }

    @Test
    void testCriteriaApi_queryWhereDynamicOr() {
        MyDataQueryDto myDataQueryDto = this.buildMyDataQueryDto();

        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        //创建query，指定返回对象类型
        CriteriaQuery<MyData> cq = cb.createQuery(MyData.class);
        Root<MyData> root = cq.from(MyData.class);

        /**
         * select id, my_name, my_type, create_time, create_by
         * from my_data
         * where
         * id = 1
         * or my_name like '%lu%'
         * or my_type = 1
         * or create_time >= '2022-01-01 18:00:20' or create_time <= '2122-01-01 18:00:20'
         * order by create_time desc
         */

        List<Predicate> predicates = new ArrayList<>(5);
        if (Objects.nonNull(myDataQueryDto.getId())) {
            predicates.add(cb.equal(root.get("id"), myDataQueryDto.getId()));
        }
        if (StringUtils.hasText(myDataQueryDto.getMyName())) {
            predicates.add(cb.like(root.get("myName"), JpaUtil.like(myDataQueryDto.getMyName())));
        }
        if (Objects.nonNull(myDataQueryDto.getMyType())) {
            predicates.add(cb.equal(root.get("myType"), myDataQueryDto.getMyType()));
        }
        if (Objects.nonNull(myDataQueryDto.getCreateTimeStart())) {
            predicates.add(cb.greaterThanOrEqualTo(root.get("createTime"), myDataQueryDto.getCreateTimeStart()));
        }
        if (Objects.nonNull(myDataQueryDto.getCreateTimeEnd())) {
            predicates.add(cb.lessThanOrEqualTo(root.get("createTime"), myDataQueryDto.getCreateTimeEnd()));
        }
        Predicate orPredicate = cb.or(predicates.stream().toArray(Predicate[]::new));

        cq
                //select所有属性（列出具体属性，并非*）
                .select(root)
                .where(orPredicate)
                .orderBy(cb.desc(root.get("createTime")));


        TypedQuery<MyData> tq = entityManager.createQuery(cq);
        List<MyData> resultList = tq.getResultList();
        //MyData singleResult = query.getSingleResult();
        log.info("myDataList size: {}, data: {}", resultList.size(), resultList);
        Assertions.assertEquals(2, resultList.size());
    }

    private MyDataQueryDto buildMyDataQueryDto() {
        MyDataQueryDto myDataQueryDto = new MyDataQueryDto();
        myDataQueryDto.setId(1L);
        myDataQueryDto.setMyName("lu");
        myDataQueryDto.setMyType(2);
        myDataQueryDto.setCreateTimeStart(DateUtil.stringToDate("2022-01-01 18:00:20"));
        myDataQueryDto.setCreateTimeEnd(new Date());
        return myDataQueryDto;
    }
}
